<?php
namespace App\Services\Rating;

use Illuminate\Support\Facades\DB;
use App\Models\System\Term;
use App\Models\System\User;
use App\Models\Rating\Check;
use App\Models\Rating\CheckTeacher;
use App\Models\Rating\CheckScore;
use App\Models\Teaching\TeachingSubject;
use App\Models\BasicTitleInfo;
use App\Models\System\Scale;
use App\Models\System\Rolepower;
use App\Models\System\ScaleDetail;
use App\Models\ActivityInfo;
use Maatwebsite\Excel\Facades\Excel;
use App\Http\Controllers\Excel\ExcelExport;
use App\Http\Controllers\Excel\CheckExport;
use App\Http\Controllers\Excel\CheckExcelExport;
use App\Models\Educaton\MasterTeacher;
use App\Models\Rating\BaseCheck;
use App\Models\Rating\CheckScoresDetail;
use App\Models\Teachers;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Facades\Auth;

class CheckService{


    /**
     * 列表
     * @author lxy
     * @datetime 2023-07-07
     */
    public function getCheckList($request)
    {
        $limit = $request['limit'] ?? 10;
        $where = [];

        $user_id = auth()->user()->id;
        $role_id = auth()->user()->cur_role_id;

        !empty($request['search']['data_version_id'])   && $where[] = ['data_version_id','=',$request['search']['data_version_id']];

        !empty($request['search']['name'])     && $where[] = ['name','like','%'.$request['search']['name'].'%'];

        if(isset($request['search']['status']) &&  $request['search']['status'] === '0'){
            $where[] = ['status','=',$request['search']['status']];
        }else if(isset($request['search']['status']) &&  $request['search']['status'] == 1){
            $where[] = ['status','=',1];
            $where[] = ['check_start','>',date('Y-m-d H:i:s')];
        }else if(isset($request['search']['status']) &&  $request['search']['status'] == 2){
            $where[] = ['status','=',1];
            $where[] = ['check_start','<=',date('Y-m-d H:i:s')];
            $where[] = ['check_end','>=',date('Y-m-d H:i:s')];
        }else if(isset($request['search']['status']) &&  $request['search']['status'] == 3){
            $where[] = ['status','=',1];
            $where[] = ['check_end','>',date('Y-m-d H:i:s')];
        }

        if(!empty($request['search']['date'])){
            $date = explode(' - ',$request['search']['date']);
            $where[] = ['check_start','>=',$date[0]];
            $where[] = ['check_end','<=',$date[1]];
        }

        $rolepower = Rolepower::where('role_id',$role_id)->pluck('actionid')->toArray();

        $result = Check::where($where)
                    ->orderBy('created_at','desc')
                    ->paginate($limit)
                    ->toArray();

        foreach($result['data'] as $k=>&$v){
            if( in_array('201',$rolepower) || (!empty($v['leader_array']) && in_array($user_id,$v['leader_array']['value'])) || (!empty($v['members_array']) && in_array($user_id,$v['members_array']['value'])) ){
                $v['is_check'] = 1; //可以审核
            }else{
                $v['is_check'] = 0; //不能审核
            }
        }

        $result['limit'] = $limit;
        return $result;
    }


    /**
     * 添加
     * @author lxy
     * @datetime 2023-03-08
     */
    public function addCheck($request)
    {

        $id = !empty($request['id'])?$request['id']:0;
        //学期名称
        $data_versions = Term::where('id',$request['data_version_id'])->first()->toArray();

        !empty($request['materials_date']) && $request['materials_date'] = json_encode($request['materials_date']);
        !empty($request['leader']) && $request['leader'] = json_encode($request['leader']);
        !empty($request['members']) && $request['members'] = json_encode($request['members']);

        if(!empty($request['files'])){
            $files = json_decode($request['files'],true);
            foreach($files as $k=>$v){
                if(empty($v)) unset($files[$k]);
            }
            $files = array_values($files);
            $request['files']  = json_encode($files);
        }else{
            unset($request['files']);
        }
        unset($request['id'],$request['file']);
        if(empty($request['check_start'])) unset($request['check_start']);
        if(empty($request['check_end'])) unset($request['check_end']);


        //已经提交过的数据不能保存
        if($request['status'] == '0'){
            if($id){
                $status = Check::where('id',$id)->value('status');
                if($status == '1'){
                    throw new \Exception('已提交过的数据不能继续保存');
                }
            }
        }

        //考评组长考评成员去重
        if(!empty($request['leader']) && !empty($request['members'])){
            $request['leader'] = json_decode($request['leader'],true);
            $request['members'] = json_decode($request['members'],true);
            foreach($request['leader'] as $k=>$v){
                foreach($request['members'] as $k2=>$v2){
                    if($v['value'] == $v2['value']){
                        unset($request['members'][$k2]);
                    }
                }
            }
            if(empty($request['members'])){
                throw new \Exception('考评组长和考评成员存在重复');
            }
            $request['check_sum'] = count($request['leader'])+count($request['members']);
            $request['leader'] = json_encode($request['leader']);
            $request['members'] = json_encode(array_values($request['members']));
        }

        //参与老师
        $request['teachers'] = !empty($request['teachers'])?json_encode($request['teachers']):'';
        //unset($request['teachers']);

        try{
            DB::beginTransaction();
            if(!$id){
                //新增
                $request['data_version_name'] = $data_versions['title'];
                $request['author_id'] = auth()->user()->id;
                $request['author_name'] = auth()->user()->nickname;
                $result = Check::create($request);

                if(!empty($request['teachers'])){
                    $data = [];
                    foreach(json_decode($request['teachers'],true) as $k=>$v){
                        $data['check_id'] = $result->id;
                        $data['user_id'] = $v['value'];
                        $data['username'] = $v['name'];
                        $title_info = BasicTitleInfo::where('user_id',$v['value'])->where('deleted',0)->first();
                        $data['title_level'] = $title_info && !empty($title_info->toArray()['title_set'])?$title_info->toArray()['title_set'][0]['selectKey']:0;
                        $data['title_name'] = $title_info && !empty($title_info->toArray()['title_set'])?$title_info->toArray()['title_set'][0]['selectValue']:'';

                        /** @tips 20247-07-19 这三位老师正高级按高级处理 */
                        if(in_array($v['username'],['hsxx_miy','hsxx_chengy','hsxx_zhanglm'])){
                            $data['title_level'] = 2;
                            $data['title_name'] = '正高级教师(属于高级)';
                        }

                        //$data['sort'] = $title_info?$title_info->toArray()['title_set'][0]['selectValue']:'';
                        $zid = CheckTeacher::insertGetId($data);
                        CheckTeacher::where('id',$zid)->update(['sort'=>$zid]);
                        $data = [];
                    }

                    //CheckTeacher::insert($data);
                }
                DB::commit();
                return $result->id;
            }else{
               // dd($request);
                //编辑
                $request['data_version_name'] = $data_versions['title'];
                $result = Check::where('id',$id)->update($request);
                if(!empty($request['teachers'])){
                    CheckTeacher::where('check_id',$id)->update(['status'=>0]);
                    foreach(json_decode($request['teachers'],true) as $k=>$v){
                        $title_info = BasicTitleInfo::where('user_id',$v['value'])->where('deleted',0)->first();
                        $title_level = $title_info && !empty($title_info->toArray()['title_set'])?$title_info->toArray()['title_set'][0]['selectKey']:0;
                        $title_name = $title_info && !empty($title_info->toArray()['title_set'])?$title_info->toArray()['title_set'][0]['selectValue']:'';

                        /** @tips 20247-07-19 这三位老师正高级按高级处理 */
                        if(in_array($v['username'],['hsxx_miy','hsxx_chengy','hsxx_zhanglm'])){
                            $title_level = 2;
                            $title_name = '正高级教师(属于高级)';
                        }

                        $res =  CheckTeacher::updateOrCreate(['check_id'=>$id,'user_id'=>$v['value']],
                        ['username'=>$v['name'],'title_level'=>$title_level,'title_name'=>$title_name,'status'=>1]);
                        CheckTeacher::where('id',$res->id)->update(['sort'=>$res->id]);
                        //dd($res->id);
                    }
                }
                DB::commit();
                return $id;
            }
        }catch(\Exception $e){
            DB::rollback();
            throw new \Exception($e->getMessage());
        }

    }

    /**
     * 复用
     * @author lxy
     * @datetime 2024-04-09
     */
    public function copyCheck($request)
    {
        ini_set('max_execution_time', 3600);
        ini_set('memory_limit', '500M');
        try{
            DB::beginTransaction();
            $id = $request['id'];
            //学期名称
            $data_versions = Term::where('id',$request['data_version_id2'])->first()->toArray();

            $check = BaseCheck::where('id',$id)->first()->toArray();
            $check['name'] = $request['name'];
            $check['data_version_name'] = $data_versions['title'];
            $check['data_version_id'] = $data_versions['id'];
            $check['materials_date'] = json_encode($request['materials_date']);
            $check['materials_text'] = $request['materials_text'];
            $check['check_start'] = $request['check_start'];
            $check['check_end'] = $request['check_end'];
            $check['is_copy'] = 1;

            unset($check['id'],$check['created_at'],$check['updated_at']);

            $copy_check = BaseCheck::create($check);

            $check_teacher = CheckTeacher::where('check_id',$id)->with('check_scores')->get();
            //dd($check_teacher->toArray());
            foreach($check_teacher as $k=>$v){
                $data1 = [];
                $data1 = $v->toArray();
                $data1['check_id'] = $copy_check->id;
                $data1['check_sums'] = 0;
                $data1['is_check'] = 0;
                $data1['check_status'] = 0;
                $data1['scores'] = 0;
                unset($data1['id'],$data1['grade_array'],$data1['subject_array'],$data1['created_at'],$data1['updated_at'],$data1['check_scores']);
                $copy_check_teacher =  CheckTeacher::create($data1);


                //$check_scores = $v->check_scores;
                //$check_scores = $v['check_scores']->toArray();
                //dd($check_scores->toArray());
                $datetime = date('Y-m-d H:i:s');
                $data2 = [];
                foreach($v['check_scores'] as $k2=>$v2){
                    $data2[$k2] = $v2->toArray();
                    $data2[$k2]['check_id'] = $copy_check->id;
                    $data2[$k2]['ct_id'] = $copy_check_teacher->id;
                    $data2[$k2]['created_at'] = $datetime;
                    $data2[$k2]['updated_at'] = $datetime;
                    $data2[$k2]['mark_scores'] = 0;
                    unset($data2[$k2]['id'],$data2[$k2]['scores_detail_array']);


                    $check_scores_details = $v2->check_scores_details()->where('author_id',$v2->author_id)->select('author_id','scale_top_id','scale_id','scores')->get()->toArray();
                    $data3 = [];
                    foreach($check_scores_details as $k3=>$v3){
                        $data3[$k3] = $v3;
                        $data3[$k3]['check_id'] = $copy_check->id;
                        $data3[$k3]['ct_id'] = $copy_check_teacher->id;
                        $data3[$k3]['created_at'] = $datetime;
                        $data3[$k3]['updated_at'] = $datetime;
                    }
                    if($data3){
                        CheckScoresDetail::insert($data3);
                    }
                    //dd($data3);
                    //dd($v2->toArray(),$data2);
                }
                //dd($data2);
                CheckScore::insert($data2);
                //dd($check_scores);

            }

            DB::commit();
            return [];
           //dd($check_teacher);
        }catch(\Exception $e){
            DB::rollback();
            throw new \Exception($e->getMessage());
        }


    }

    /**
     * 选择量表
     * @author lxy
     * @datetime 2023-03-10
     */
    public function getCheckScale($request)
    {
        return Scale::select('id','name')->where('type',2)->get()->toArray();
    }

    /**
     * 获取一条数据
     * @author lxy
     * @datetime 2023-07-07
     */
    public function getCheckFirst($request)
    {
        $id = $request['id'];
        $result = Check::where('id',$id)->first();
        if(!$result){
            return [];
        }
        return $result->toArray();
    }

    /**
     * 获取申报教师列表
     * @author lxy
     * @datetime 2023-07-10
     */
    public function getCheckTeachersList($request)
    {
        $id = $request['id'];
        $limit = $request['limit'] ?? 10;
        $where[] = ['check_teachers.check_id','=',$id];
        $where[] = ['check_teachers.status','=',1];

        $is_captain2 = $request['is_captain'];
        $is_admin_role = $request['is_admin_role'];

        $user_id = auth()->user()->id;
        $subjectId  = 0;
        $gradeId  = 0;
        $sort = [];
        $ranks = [];
        $title_sort = [];

        !empty($request['search']['name'])     && $where[] = ['check_teachers.username','like','%'.$request['search']['name'].'%'];
        !empty($request['search']['subject']) && $subjectId = $request['search']['subject'];
        !empty($request['search']['grade']) && $gradeId = $request['search']['grade'];
        !empty($request['search']['title']) && $where[] = ['check_teachers.title_level','=',$request['search']['title']];
        //(isset($request['search']['check_status']) && $request['search']['check_status'] != '') && $where[] = ['check_teachers.check_status','=',$request['search']['check_status']];

        if(isset($request['search']['check_status']) && $request['search']['check_status'] != ''){
            $check_status = $request['search']['check_status'];
        }else{
            $check_status = null;
        }

        //分数排序
        if(!empty($request['search']['sort'])){
            $sort = explode('|',$request['search']['sort']);
        }
        //分数排序
        if(!empty($request['scores'])){
            $sort = explode('|',$request['scores']);
        }
        if(!empty($request['title_sort'])){
            $title_sort = explode('|',$request['title_sort']);
        }
        if(!empty($request['ranks'])){
            $ranks = explode('|',$request['ranks']);
        }

        $check = Check::where('id',$id)->first()->toArray();
       // DB::connection()->enableQueryLog();
        $result = CheckTeacher::where($where)
                ->with(['checks:id,check_sum',
                    'teaching_subject'=>function($q) use($check){
                        $q->where('data_version_id',$check['data_version_id'])->where('deleted',0)
                        ->select('id','user_id','subject_dic_items','grade_dic_items','class_item');
                    },'master_teacher'=>function($q) use($check){
                        $q->where('data_version_id',$check['data_version_id'])->where('deleted',0)
                        ->select('user_id','grade_value','class_value');
                    },'check_score'=>function($q) use($user_id){
                        $q->where('author_id',$user_id)->select('id','ct_id','author_id','scores','average_score','mark_scores');
                    }])
                ->when($check_status !== null,function($q) use($is_admin_role,$check_status,$user_id){
                    if($is_admin_role == 1){
                        $q->where('check_teachers.check_status',$check_status);
                    }else{
                        if($check_status==1){
                            $q->whereHas('check_score',function(Builder $query) use($check_status,$user_id){
                                $query->where('mark_scores',$check_status)->where('author_id',$user_id);
                            });
                        }else{
                            $q->whereDoesntHave('check_score',function(Builder $query) use($check_status,$user_id){
                                $query->where('mark_scores',1)->where('author_id',$user_id);
                            });
                        }
                    }
                })
                ->when($subjectId > 0 || $gradeId > 0, function($q) use($subjectId,$check,$gradeId) {
                    $q->whereHas('teaching_subject', function (Builder $query) use($subjectId,$check,$gradeId) {
                        if($subjectId>0){
                            $query->whereRaw(" JSON_EXTRACT(`subject_dic_items`,'$[0].optionId') = $subjectId");
                        }
                        if($gradeId > 0){
                            $query->whereRaw(" JSON_EXTRACT(`grade_dic_items`,'$[0].optionId') = $gradeId");
                        }
                        $query->where('data_version_id',$check['data_version_id'])->where('deleted',0);
                    });
                })
                ->when($title_sort,function($q) use($title_sort){
                    $q->orderBy('title_level',$title_sort[1]);
                })
                ->when($is_captain2 == 1,function($q) use($sort,$ranks){
                    //dd($sort,$ranks);
                    if($sort || $ranks){
                        $q->orderBy('is_check','desc');
                        if($sort){
                            $q->orderBy($sort[0],$sort[1])->orderBy('check_time',$sort[1]);  //特别注意，加上审核时间排序，分数相同的数据不会变动
                        }
                        if($ranks){
                            $q->orderBy($ranks[0],$ranks[1])->orderBy('check_time',$ranks[1]);
                        }
                        //dd($q);
                    }
                })
                ->when($is_captain2 == 2,function($q) use($sort,$user_id,$ranks){
                    //小组,分数 排名排序
                    if($sort || $ranks){
                        $q->leftJoin('check_scores as b','check_teachers.id','=','b.ct_id')->where('b.author_id',$user_id);
                        if($sort){
                            $q->orderBy('b.scores',$sort[1]);
                        }
                        if($ranks){
                            $q->orderBy('b.scores',$ranks[1]);
                        }
                        $q->select('check_teachers.*','b.scores as ct_scores');
                    }
                })
                ->orderBy('sort')
                ->paginate($limit)
                ->toArray();
                // dd(DB::getQueryLog());

        //考评组长和成员才能审核  @tips 2024-08-02 已结束只有校长角色可以审核
        if((!empty($check['leader_array']) && in_array($user_id,$check['leader_array']['value']))  ||
            (!empty($check['members_array']) && in_array($user_id,$check['members_array']['value']))
        ){
            $is_check_role = 1; //可以审核
        }else{
            $is_check_role = 0; //不能审核
        }
        $role_id = auth()->user()->cur_role_id;
        if($role_id == 6){
            $is_xz = 1; //可以审核
        }else{
            $is_xz = 0; //不能审核
        }

        $is_captain = 0; //1-组长 2-小组成员 能查看全部小组成员评分
        if((!empty($check['leader_array']) && in_array($user_id,$check['leader_array']['value']))){
            $is_captain = 1;
        }else if((!empty($check['members_array']) && in_array($user_id,$check['members_array']['value']))){
            $is_captain = 2;
        }else{
            $is_captain = 0;
        }
        if(is_checker()){
            $is_captain = 1;
            $result['is_admin_role'] = 1;
        }else{
            $result['is_admin_role'] = 0;
        }

        //获取分数相同重分数组
        $s = $this->get_scores_eq($id,$user_id);
        //dd($result['data']);
        foreach($result['data'] as $k=>&$v){
            $v['is_check_role'] = $is_check_role;
            $v['is_xz'] = $is_xz;
            $v['is_captain'] = $is_captain;
            $v['scale_id'] = $check['scale_id'];
            $rk = $this->get_ranks($v['check_id'],$v['user_id'],$v['title_level'],$v['id']);
            //dd( $rk);
            $v['ranks'] = $rk;
            $v['subject'] = !empty($v['subject_array'])?implode(',',array_unique(array_column($v['subject_array'],'selectValue'))):'';
            if(!empty($v['master_teacher']['class_set'])){
                if(!empty($v['master_teacher']['grade_set'])){
                    $v['class'] = $v['master_teacher']['grade_set'][0]['selectValue'].$v['master_teacher']['class_set'][0]['selectValue'];
                }else{
                    $v['class'] = $v['master_teacher']['class_set'][0]['selectValue'];
                }
            }else{
                $v['class'] = '';
            }
            $v['grade'] = !empty($v['grade_array'])?implode(',',array_column($v['grade_array'],'selectValue')):'';

            //考评组长分数相同标记红色处理
            if($v['scores'] && $v['title_level'] && !empty($s['a'][$v['title_level']][(string)$v['scores'].'_']) && $s['a'][$v['title_level']][(string)$v['scores'].'_']>=2){
                $v['is_scores_eq'] = 1; //分数是否相等 1相等
            }

            //考评成员分数相同标记红色处理
            if($v['check_score'] && $v['title_level'] && !empty($s['b'][$v['title_level']][(string)$v['check_score']['scores'].'_']) && $s['b'][$v['title_level']][(string)$v['check_score']['scores'].'_']>=2){
                $v['check_score']['is_scores_eq'] = 1; //分数是否相等 1相等
            }

        }

        //分数相同排序处理
        if($is_captain == 2 && ( ($sort && $sort[1] == 'desc') || (!$sort && $ranks && $ranks[1] == 'desc') )){
            if(!empty($request['search']['title'])){
                usort($result['data'],function($a,$b){
                    return ($a['ranks']['gr_rank'] > $b['ranks']['gr_rank']) ? 1 : -1;
                });
            }
        }
        if($is_captain == 2 && ( ($sort && $sort[1] == 'asc') || (!$sort && $ranks && $ranks[1] == 'asc') )){
            if(!empty($request['search']['title'])){
                usort($result['data'],function($a,$b){
                    return ($a['ranks']['gr_rank'] < $b['ranks']['gr_rank']) ? 1 : -1;
                });
            }
        }

        $result['limit'] = $limit;

        return $result;
    }



    /**
     * 获取教师基础信息
     * @author lxy
     * @datetime 2023-07-10
     */
    public function getTeacherBaseInfo($request)
    {
        $id = $request['id'];
        $ct_id = $request['ct_id'];
        $user_id = $request['user_id'];
        //$user_id = auth()->user()->id;
        $check = Check::where('id',$id)->with(['check_teacher'=>function($q) use($user_id){
                    $q->where('user_id',$user_id)->with(['check_score'=>function($q){
                        $q->where('author_id',auth()->user()->id);
                    }]);
                }])->first()->toArray();
               // dd($check);
        $username = User::where('id',$user_id)->value('nickname');
        $teaching_subject = TeachingSubject::where('data_version_id',$check['data_version_id'])->where('user_id',$user_id)->where('deleted',0)->get();

        $master_teacher = MasterTeacher::where('data_version_id',$check['data_version_id'])->where('user_id',$user_id)->first();
        $master_teacher = $master_teacher?$master_teacher->toArray():[];
        $class = '';
        if(!empty($master_teacher['class_set'])){
            if(!empty($master_teacher['grade_set'])){
                $class = $master_teacher['grade_set'][0]['selectValue'].$master_teacher['class_set'][0]['selectValue'];
            }else{
                $class = $master_teacher['class_set'][0]['selectValue'];
            }
        }else{
            $class = '';
        }

        $title_info = BasicTitleInfo::where('user_id',$user_id)->where('deleted',0)->first();
        //dd($title_info->toArray());

        if(!empty($check['check_teacher']) && !empty($check['check_teacher']['check_score'])){
            if(!$check['check_teacher']['is_check']){
                $zf = $check['check_teacher']['check_score']['scores']; //总分
                $pjf = $check['check_teacher']['check_score']['average_score']; //平均分
                $pm = ''; //排名
            }else{
                $zf = $check['check_teacher']['scores']; //总分
                $pjf = $check['check_teacher']['average_score']; //平均分
                $pm = $this->get_ranks($id,$user_id,$check['check_teacher']['title_level'],$ct_id);
            }
        }else{
            $zf = '';
            $pjf = '';
            $pm = '';
        }

        $grade = $teaching_subject->pluck('grade_set.grade_items')->flatten(1)->unique('selectValue')->sortBy('optionId')->toArray();
        $subject = $teaching_subject->pluck('subject_set')->flatten(1)->toArray();

        return [
            'username' => $username,
            'data_version_id' => $check['data_version_id'],
            'data_version_name' => $check['data_version_name'],
            'subject' => $subject?implode(',',array_unique(array_column($subject,'selectValue'))):'',
            'class' => $class,
            'grade' => $grade?implode(',',array_column($grade,'selectValue')):'',
            'title' => $title_info && !empty($title_info->toArray()['title_set'])?$title_info->toArray()['title_set'][0]['selectValue']:'',
            'zf' => $zf,
            'pjf' => $pjf,
            'pm' => $pm,
            'leader_array'=>$check['leader_array'],
            'members_array'=>$check['members_array'],
        ];


    }

    /**
     * 获取评分量表
     * @author lxy
     * @datetime 2023-07-10
     */
    public function getCheckScaleInfo($request)
    {
        $scale_id = $request['scale_id'];
        $id = $request['id'];
        $user_id = $request['user_id'];
        $ct_id = $request['ct_id'];
       //dd($user_id);
        $check = Check::where('id',$id)->first()->toArray();

        $ScaleDetail = ScaleDetail::whereIsEnd(1)
                    ->where('scale_id',$scale_id)
                    ->orderBy('top_id', 'asc')
                    ->orderBy('sort', 'asc')
                    ->orderBy('pid', 'asc')
                    ->with('parent')
                    ->get()->toArray();

        /** @var \App\Repositories\System\ScaleRepository $scaleRepository */
        $scaleRepository = app('scaleRepository');
        $table_data = $scaleRepository->toTableData2($ScaleDetail);

        //获取当前用户打分
        $scores = CheckScore::where([
                    ['check_id','=',$id],
                    ['ct_id','=',$ct_id],
                    ['author_id','=',auth()->user()->id],
                ])
                ->first();
        $scores = $scores?$scores->toArray():[];
        //dd($scores);

        if($table_data){
            //dd($table_data);
            foreach($table_data as $key=>$value){
                if($value['check_module_text'] != '无'){
                    foreach(json_decode($table_data[$key]['check_module'],true) as $k=>$v ){
                        switch ($v){
                            case "4":
                                //经验交流
                                $count =  ActivityInfo::select('activity_info.*', 'b.status')
                                        ->leftJoin('form_instance as b' ,'activity_info.form_instance_id','=','b.id')
                                        ->whereIn('activity_info.activity_category',['TEARRESEARCH_EXCHANGE_TABLE','EW_EXCHANGE_EXPERIENCE_TABLE'])
                                        ->where('b.status','PASS')
                                        ->whereIn('activity_info.data_version_id',$check['materials_array'])
                                        ->where('activity_info.deleted', 0)
                                        // ->when($user_id, function($q) use($user_id){
                                        //     $q->whereHas('participants', function($query) use($user_id){
                                        //         $query->where('user_id', $user_id)->where('');
                                        //     });
                                        // })
                                        ->where('activity_info.creator_id',$user_id)
                                        ->get()->count();
                                        $table_data[$key]['module_info'][$k]['module_id'] = $v;
                                        $table_data[$key]['module_info'][$k]['count'] = $count;
                                        $table_data[$key]['module_info'][$k]['name'] = '经验交流';
                                        $table_data[$key]['module_info'][$k]['materials_array'] = json_encode($check['materials_array']);
                                break;
                            case "5":
                                //课题研究
                                //DB::connection()->enableQueryLog();
                                $count = ActivityInfo::select('activity_info.*', 'ss.status', 'b.status as old_status')
                                        ->leftJoin('form_instance as b' ,'activity_info.form_instance_id','=','b.id')
                                        ->leftJoin('participant as d','activity_info.id','=','d.activity_info_id')
                                        ->leftJoin('study_stage as ss', function ($join) {
                                            //$join->on('activity_info.id', '=', 'ss.activity_id')->on('activity_info.stage', '=', 'ss.stage');
                                            $join->on('activity_info.id', '=', 'ss.activity_id');
                                        })
                                        ->whereIn('activity_info.activity_category',['TEARRESEARCH_SUBJECT_STUDY_TABLE','EW_SUBJECT_RESEARCH_TABLE'])
                                        ->whereIn('activity_info.data_version_id',$check['materials_array'])
                                        ->whereRaw(" ( (ss.stage =4 and ss.status = 2 ) or  b.status = 'PASS') ")
                                        ->where('activity_info.deleted', 0)
                                        // ->when($user_id, function($q) use($user_id){
                                        //     $q->whereHas('participants', function($query) use($user_id){
                                        //         $query->where('user_id', $user_id);
                                        //     });
                                        // })
                                        ->whereRaw("(d.user_id={$user_id})")
                                        ->groupBy('activity_info.id')
                                        ->get()->count();
                                        //dd(DB::getQueryLog());
                                        $table_data[$key]['module_info'][$k]['module_id'] = $v;
                                        $table_data[$key]['module_info'][$k]['count'] = $count;
                                        $table_data[$key]['module_info'][$k]['name'] = '课题研究';
                                        $table_data[$key]['module_info'][$k]['materials_array'] = json_encode($check['materials_array']);
                                break;
                            case "6":
                                //论文发表
                                $count =  ActivityInfo::select('activity_info.*', 'b.status')
                                        ->leftJoin('form_instance as b' ,'activity_info.form_instance_id','=','b.id')
                                        ->leftJoin('participant as d','activity_info.id','=','d.activity_info_id')
                                        ->whereIn('activity_info.activity_category',['EW_PAPER_PUBLISH_TABLE','TEARRESEARCH_PUBLISH_TABLE'])
                                        ->where('b.status','PASS')
                                        ->whereIn('activity_info.data_version_id',$check['materials_array'])
                                        ->whereRaw(" JSON_EXTRACT(activity_info.`pubications_publish_type`,'$[0].optionId') = 128")
                                        ->where('activity_info.deleted', 0)
                                        // ->when($user_id, function($q) use($user_id){
                                        //     $q->whereHas('participants', function($query) use($user_id){
                                        //         $query->where('user_id', $user_id);
                                        //     });
                                        // })
                                        ->whereRaw("(d.user_id={$user_id})")
                                        ->groupBy('activity_info.id')
                                        ->get()->count();
                                        $table_data[$key]['module_info'][$k]['module_id'] = $v;
                                        $table_data[$key]['module_info'][$k]['count'] = $count;
                                        $table_data[$key]['module_info'][$k]['name'] = '论文发表';
                                        $table_data[$key]['module_info'][$k]['materials_array'] = json_encode($check['materials_array']);
                                break;
                            case "7":
                                //论著发表
                               // DB::connection()->enableQueryLog();
                                $count =  ActivityInfo::select('activity_info.*', 'b.status')
                                        ->leftJoin('form_instance as b' ,'activity_info.form_instance_id','=','b.id')
                                        ->leftJoin('participant as d','activity_info.id','=','d.activity_info_id')
                                        ->whereIn('activity_info.activity_category',['EW_PAPER_PUBLISH_TABLE','TEARRESEARCH_PUBLISH_TABLE'])
                                        ->where('b.status','PASS')
                                        ->whereIn('activity_info.data_version_id',$check['materials_array'])
                                        ->whereRaw(" JSON_EXTRACT(activity_info.`pubications_publish_type`,'$[0].optionId') = 129")
                                        ->where('activity_info.deleted', 0)
                                        // ->when($user_id, function($q) use($user_id){
                                        //     $q->whereHas('participants', function($query) use($user_id){
                                        //         $query->where('user_id', $user_id);
                                        //     });
                                        // })
                                        ->whereRaw("(d.user_id={$user_id})")
                                        ->groupBy('activity_info.id')
                                        ->get()->count();
                                       // dd(DB::getQueryLog());
                                        $table_data[$key]['module_info'][$k]['module_id'] = $v;
                                        $table_data[$key]['module_info'][$k]['count'] = $count;
                                        $table_data[$key]['module_info'][$k]['name'] = '论著发表';
                                        $table_data[$key]['module_info'][$k]['materials_array'] = json_encode($check['materials_array']);
                                break;
                            case "19":
                                //带教情况
                                //$username =  User::where('id',$user_id)->value('nickname');
                                $count =  ActivityInfo::select('activity_info.*')
                                        ->leftJoin('form_instance as b' ,'activity_info.form_instance_id','=','b.id')
                                        ->leftJoin('event_statistics as c','c.activity_info_id','=','activity_info.id')
                                        ->leftJoin('participant as d','activity_info.id','=','d.activity_info_id')
                                        ->where('activity_info.activity_category','IMPORTANTWORK_TEACHING_SITUATION_TABLE')
                                        ->where('b.status','PASS')
                                        ->whereIn('activity_info.data_version_id',$check['materials_array'])
                                        ->where('activity_info.deleted', 0)
                                        //->whereRaw("(activity_info.teacher_name like '%{$username}%' or d.user_id={$user_id})")
                                        ->where('activity_info.creator_id',$user_id)
                                        ->get()->count();
                                $table_data[$key]['module_info'][$k]['module_id'] = $v;
                                $table_data[$key]['module_info'][$k]['count'] = $count;
                                $table_data[$key]['module_info'][$k]['name'] = '带教情况';
                                $table_data[$key]['module_info'][$k]['materials_array'] = json_encode($check['materials_array']);
                                break;
                        }
                   }
                }
            }
            //dd($table_data);
            foreach($table_data as $kk=>&$vv){
                if($vv['check_module_text'] != '无'){
                    if(!empty($vv['module_info'])){
                        $s = 0;
                        foreach($vv['module_info'] as $k2=>$v2){
                            $s += $v2['count'];
                        }
                        if($s == 0){
                            $vv['default_score'] = 1;
                        }else if($s == 1){
                            $vv['default_score'] = 5;
                        }else{
                            $vv['default_score'] = 10;
                        }

                        //特殊处理 教育教学研究活动与工作 与 教育专题、课题研究与成果 默认分数相同
                        if(count($vv['module_info']) >1){
                            $table_data[$kk-1]['default_score'] = $vv['default_score'];
                        }
                    }
                }
                $vv['jc_score_json'] = ['scale_id'=>$vv['id'],'scores'=>$vv['default_score'],'top_id'=>$vv['top_id']]; //基础分数据
                //得分
                if($scores){
                    foreach($scores['scores_detail_array'] as $sk=>$sv){
                        //dd($sv['scale_id'],$vv['id']);
                        if($sv['scale_id'] == $vv['id']){
                            $vv['pf_score_json'] = $sv;
                            break;
                        }else{
                            $vv['pf_score_json'] = '';
                        }
                    }
                }else{
                    $vv['pf_score_json'] = '';
                }
            }

        }
        //dd($table_data);
        return ['table_data'=>$table_data?$table_data:[]];
        //dd($table_data);
    }

    /**
     * 打分
     * @author lxy
     * @datetime 2023-07-11
     */
    public function checkScores($request)
    {
        $user = auth()->user();
        $check_id = $request['id'];
        $ct_id = $request['ct_id'];
        $user_id = $request['user_id'];
        $is_pass = isset($request['is_pass'])?$request['is_pass']:0;
        //处理分数
        $scores = 0;
        if(!empty($request['scores2'])){
            foreach($request['scores2'] as $k3=>&$v3){
                $v3 = json_decode($v3,true);
                $scores += $v3['scores'];
            }
        }
        $pj_score = round($scores/19,2);

        try{
            DB::beginTransaction();
            //当老师的分数出现重分情况时：做出以下判断：提示1：如：您提交的分数是xx分，与张三老师的分数出现重分情况
            $tips = [];
            if($is_pass != 1){
                //同职称下分数相同
                $tl = CheckTeacher::where('id',$ct_id)->value('title_level');
                if($tl){
                    $ct_in = CheckTeacher::where('check_id',$check_id)->where('status',1)->where('title_level',$tl)->pluck('id')->toArray();
                    if(!empty($ct_in)){
                        $str = "您提交的分数是<span style='color:red' >{$scores}</span>分";
                        $str1 =  $str2 ='';
                        $tip2 = CheckScore::where('check_scores.check_id',$check_id)->whereIn('check_scores.ct_id',$ct_in)->where('check_scores.scores',$scores)->where('check_scores.mark_scores',1)
                        ->where('check_scores.author_id',$user->id)
                        ->leftJoin('check_teachers as b','check_scores.ct_id','=','b.id')
                        ->select('check_scores.author_id','check_scores.scores','check_scores.ct_id','b.username')->get()->toArray();
                        if(!empty($tip2)){
                            $str1 =  "，与被评老师<span style='color:red' >".implode('，',array_column($tip2,'username'))."</span>老师的分数出现重分情况";
                        }
                        // $tip1 = CheckScore::where('check_scores.check_id',$check_id)->whereIn('check_scores.ct_id',$ct_in)->where('check_scores.scores',$scores)
                        // ->where('check_scores.author_id','!=',$user->id)
                        // ->leftJoin('users as b','check_scores.author_id','=','b.id')
                        // ->select('check_scores.author_id','check_scores.scores','check_scores.ct_id','b.nickname')->get()->toArray();
                        // if(!empty($tip1)){
                        //     $tip1 = array_column($tip1,null,'author_id');
                        //     $str2 = "，与评审成员<span style='color:red' >".implode('，',array_column($tip1,'nickname'))."</span>老师的分数出现重分情况";
                        // }
                        if($str1 || $str2){
                            $tips[] = $str.$str1.$str2.'。';
                        }
                    }
                }
            }

            $level  = 0;
            //计算当前考核成员评分的档次职称 包括（高级，一级，二级）优，合格，基本合格，不合格
            $checkt = CheckTeacher::where('id',$ct_id)->whereIn('title_level',[1,2,3,4])->first();
            if($checkt){
                $checkt = $checkt->toArray();
                $title_level = $checkt['title_level'];
                $level = $this->get_level($title_level,$pj_score); //职称档次
                //触发 提示2：高级教师队伍人数：21人当前老师职称：高级教师；分数：10分（优等），优等人数已超过7人；
                if($is_pass != 1){
                    $title_str = '';
                    switch($title_level){
                        case 1: $title_str = '正高级教师'; break;
                        case 2: $title_str = '高级教师'; break;
                        case 3: $title_str = '一级教师'; break;
                        case 4: $title_str = '二级教师'; break;
                    }
                    $js = CheckTeacher::where('check_id',$check_id)->where('title_level',$title_level)->where('status',1)->pluck('id')->toArray();
                    $gjjs_sum = count($js); //教师总人数
                    //$ydjs_bl = ceil($gjjs_sum*0.3); //优等教师比列数 进一取整
                    $ydjs_bl = ceil($gjjs_sum*0.2); //优等教师比列数 进一取整   @tips 2024-07-16 占比改成20%
                    //DB::connection()->enableQueryLog();
                    $ydjs_sum = CheckScore::where('check_id',$check_id)->whereIn('ct_id',$js)->where('author_id',auth()->user()->id)->where('level',1)->where('mark_scores',1)->count(); //优等教师人数
                    //dd($ydjs_bl);
                    if($level == 1 && $ydjs_sum>=$ydjs_bl){
                        $tips[] = "{$title_str}队伍人数：<span style='color:red' >{$gjjs_sum}</span>人；当前老师职称：{$title_str}；分数：<span style='color:red' >{$pj_score}</span>分（优等），优等人数已超过<span style='color:red' >{$ydjs_bl}</span>人。";
                    }
                }
            }

            if(!empty($tips)){
                return [
                    'tips' => $tips
                ];
            }

            //插入打分详情
            CheckScore::updateOrCreate(['check_id'=>$check_id,'ct_id'=>$ct_id,'author_id'=>$user->id],
                    ['scores'=>$scores,'scores_detail'=>json_encode($request['scores2']),'average_score'=>$pj_score,'level'=>$level,'mark_scores'=>1]
                    );

            //插入分数详情
            $dscores_array = [];
            foreach($request['scores2'] as $k=>$v){
                $dscores_array[$k]['check_id'] = $check_id;
                $dscores_array[$k]['ct_id'] = $ct_id;
                $dscores_array[$k]['author_id'] = $user->id;
                $dscores_array[$k]['scale_top_id'] = $v['top_id'];
                $dscores_array[$k]['scale_id'] = $v['scale_id'];
                $dscores_array[$k]['scores'] = $v['scores'];
                $dscores_array[$k]['created_at'] = date('Y-m-d H:i:s');
                $dscores_array[$k]['updated_at'] = date('Y-m-d H:i:s');
            }
            CheckScoresDetail::where('check_id',$check_id)->where('ct_id',$ct_id)->where('author_id',$user->id)->delete();
            CheckScoresDetail::insert($dscores_array);

            //打分人数自增
            CheckTeacher::where('id',$ct_id)->update(['check_sums'=>CheckScore::where('ct_id',$ct_id)->where('mark_scores',1)->count(),'check_status'=>1]);

            $checkTeacher = CheckTeacher::where('id',$ct_id)->with('checks:id,check_sum')->first();
            $checkTeacher = $checkTeacher?$checkTeacher->toArray():[];
            //全部打分完成 计算总分，平均分
            //if($checkTeacher && $checkTeacher['check_sums'] == $checkTeacher['checks']['check_sum']){
                $zf = CheckScore::where('ct_id',$ct_id)->where('mark_scores',1)->sum('scores'); //当前成员打分总分
                $zf = round($zf/$checkTeacher['check_sums'],2); //总分  总分/评分人数量
                $pjf = round($zf/19,2); //平均分
                $is_check = $checkTeacher && $checkTeacher['check_sums'] == $checkTeacher['checks']['check_sum']?1:0;//全部打分完成 修改is_check状态
                CheckTeacher::where('id',$ct_id)->update(['scores'=>$zf,'average_score'=>$pjf,'is_check'=>$is_check,'check_time'=>date('Y-m-d H:i:s')]);
            //}

            DB::commit();
            return [
                'tips' => $tips,
            ];

        }catch(\Exception $e){
            DB::rollback();
            throw new \Exception($e->getMessage());
        }
    }

    /**
     * 删除组员
     * @author lxy
     * @datetime 2024-06-18
     */
    public function checkMemberDel($request)
    {
        $id = $request['id'];
        $author_id = $request['value'];
        $type = $request['type'];
        try{
            DB::beginTransaction();

            //打分人数自减
            $csData = CheckScore::where('check_id',$id)->where('author_id',$author_id)->where('mark_scores',1)->get()->toArray();
            foreach($csData as $k2=>$v2){
                CheckTeacher::where('id',$v2['ct_id'])->decrement('check_sums');
            }

            //删除打分表
            CheckScore::where('check_id',$id)->where('author_id',$author_id)->delete();

            //删除打分详情表
            CheckScoresDetail::where('check_id',$id)->where('author_id',$author_id)->delete();

            //打分总人数自减
            BaseCheck::where('id',$id)->decrement('check_sum');

            //重新计算分数
            $result = CheckTeacher::where('check_id',$id)->with('checks:id,check_sum')->get()->toArray();
            foreach($result as $k=>$v){
                $zf = CheckScore::where('ct_id',$v['id'])->where('mark_scores',1)->sum('scores'); //当前成员打分总分
                $zf = ($zf && $v['check_sums'])?round($zf/$v['check_sums'],2):0; //总分  总分/评分人数量
                $pjf = $zf?round($zf/19,2):0; //平均分
                //更新分数
                CheckTeacher::where('id',$v['id'])->update(['scores'=>$zf,'average_score'=>$pjf]);
            }

            //删除审核组人员
            $check = BaseCheck::where('id',$id)->select('id','leader','members')->first()->toArray();
            if($type == 'leader'){
                $cy = $check['leader'];
            }else{
                $cy = $check['members'];
            }
            foreach($cy as $k=>$v){
                if($v['value'] == $author_id){
                    unset($cy[$k]);
                }
            }
            $cy = !empty($cy)?array_values($cy):null;
            if($type == 'leader'){
                BaseCheck::where('id',$id)->update(['leader'=>$cy]);
            }else{
                BaseCheck::where('id',$id)->update(['members'=>$cy]);
            }

            DB::commit();
            return BaseCheck::where('id',$id)->select('id','leader','members')->first()->toArray();
        }catch(\Exception $e){
            DB::rollback();
            throw new \Exception($e->getMessage());
        }
    }

    /**
     * 获取组员
     * @author lxy
     * @datetime 2024-06-18
     */
    public function getcheckMember($request)
    {
        $result = User::where('nickname','like','%'.$request['name'].'%')->where('deleted',0)->where('disabled',0)->where('username','like','%\_%')->select('id as value','group_id','nickname as name','username')->get()->toArray();
        return $result;
    }

    /**
     * 添加审核成员
     * @author lxy
     * @datetime 2024-06-19
     */
    public function addCheckMember($request)
    {
        $id = $request['id'];
        $value = $request['value'][0];
        $type = $request['type'];
        unset($value['username']);
        try{
            DB::beginTransaction();
            $check = BaseCheck::where('id',$id)->select('id','leader','members')->first()->toArray();
            if($type == 'leader'){
                $cy = $check['leader'];
            }else{
                $cy = $check['members'];
            }
            if(!$cy){
                if($type == 'leader'){
                    BaseCheck::where('id',$id)->update(['leader'=>[$value]]);
                }else{
                    BaseCheck::where('id',$id)->update(['members'=>[$value]]);
                }
            }else{
                $a = array_column($cy,'value');
                if(in_array($value['value'],$a)){
                    throw new \Exception('当前人员已存在');
                }
                array_push($cy,$value);
                if($type == 'leader'){
                    BaseCheck::where('id',$id)->update(['leader'=>$cy]);
                }else{
                    BaseCheck::where('id',$id)->update(['members'=>$cy]);
                }
            }

            //是否全部评分完成  否
            CheckTeacher::where('check_id',$id)->update(['is_check'=>0]);
            //打分总人数自增
            BaseCheck::where('id',$id)->increment('check_sum');

            DB::commit();
            return BaseCheck::where('id',$id)->select('id','leader','members')->first()->toArray();
        }catch(\Exception $e){
            DB::rollback();
            throw new \Exception($e->getMessage());
        }
    }

    /**
     * 获取最终认定
     * @author lxy
     * @datetime 2023-07-12
     */
    public function getFinalScores($request)
    {
        $scale_id = $request['scale_id'];
        $id = $request['id'];
        $user_id = $request['user_id'];
        $ct_id = $request['ct_id'];
        $check = Check::where('id',$id)->first()->toArray();
        $checkTeacher = CheckTeacher::where('id',$ct_id)->first();

        $ScaleDetail = ScaleDetail::whereIsEnd(1)
                    ->where('scale_id',$scale_id)
                    ->orderBy('top_id', 'asc')
                    ->orderBy('sort', 'asc')
                    ->orderBy('pid', 'asc')
                    ->with('parent')
                    ->get()->toArray();

        /** @var \App\Repositories\System\ScaleRepository $scaleRepository */
        $scaleRepository = app('scaleRepository');
        $table_data = $scaleRepository->toTableData2($ScaleDetail);

        //获取所有考核成员的评分
        $checkScore = CheckScore::where('ct_id',$ct_id)->select('scores_detail')->get()->toArray();
        $scores = [];
        foreach($checkScore as $k=>$v){
            foreach($v['scores_detail_array'] as $k2=>$v2){
                $scores[$k2]['scale_id'] = $v2['scale_id'];
                $scores[$k2]['scores'][] = $v2['scores'];
                $scores[$k2]['scores_sum'] = array_sum($scores[$k2]['scores']);
            }
        }
        $scores = $scores?array_column($scores,null,'scale_id'):[];
        foreach($table_data as $key=>&$value){

            if($value['check_module_text'] != '无'){
                foreach(json_decode($table_data[$key]['check_module'],true) as $k=>$v ){
                    switch ($v){
                        case "4":
                            //经验交流
                            $count =  ActivityInfo::select('activity_info.*', 'b.status')
                                    ->leftJoin('form_instance as b' ,'activity_info.form_instance_id','=','b.id')
                                    ->whereIn('activity_info.activity_category',['TEARRESEARCH_EXCHANGE_TABLE','EW_EXCHANGE_EXPERIENCE_TABLE'])
                                    ->where('b.status','PASS')
                                    ->whereIn('activity_info.data_version_id',$check['materials_array'])
                                    ->where('activity_info.deleted', 0)
                                    // ->when($user_id, function($q) use($user_id){
                                    //     $q->whereHas('participants', function($query) use($user_id){
                                    //         $query->where('user_id', $user_id)->where('');
                                    //     });
                                    // })
                                    ->where('activity_info.creator_id',$user_id)
                                    ->get()->count();
                                    $table_data[$key]['module_info'][$k]['module_id'] = $v;
                                    $table_data[$key]['module_info'][$k]['count'] = $count;
                                    $table_data[$key]['module_info'][$k]['name'] = '经验交流';
                                    $table_data[$key]['module_info'][$k]['materials_array'] = json_encode($check['materials_array']);
                            break;
                        case "5":
                            //课题研究
                            //DB::connection()->enableQueryLog();
                            $count = ActivityInfo::select('activity_info.*', 'ss.status', 'b.status as old_status')
                                    ->leftJoin('form_instance as b' ,'activity_info.form_instance_id','=','b.id')
                                    ->leftJoin('participant as d','activity_info.id','=','d.activity_info_id')
                                    ->leftJoin('study_stage as ss', function ($join) {
                                        //$join->on('activity_info.id', '=', 'ss.activity_id')->on('activity_info.stage', '=', 'ss.stage');
                                        $join->on('activity_info.id', '=', 'ss.activity_id');
                                    })
                                    ->whereIn('activity_info.activity_category',['TEARRESEARCH_SUBJECT_STUDY_TABLE','EW_SUBJECT_RESEARCH_TABLE'])
                                    ->whereIn('activity_info.data_version_id',$check['materials_array'])
                                    ->whereRaw(" ( (ss.stage =4 and ss.status = 2 ) or  b.status = 'PASS') ")
                                    ->where('activity_info.deleted', 0)
                                    // ->when($user_id, function($q) use($user_id){
                                    //     $q->whereHas('participants', function($query) use($user_id){
                                    //         $query->where('user_id', $user_id);
                                    //     });
                                    // })
                                    ->whereRaw("(d.user_id={$user_id})")
                                    ->groupBy('activity_info.id')
                                    ->get()->count();
                                    //dd(DB::getQueryLog());
                                    $table_data[$key]['module_info'][$k]['module_id'] = $v;
                                    $table_data[$key]['module_info'][$k]['count'] = $count;
                                    $table_data[$key]['module_info'][$k]['name'] = '课题研究';
                                    $table_data[$key]['module_info'][$k]['materials_array'] = json_encode($check['materials_array']);
                            break;
                        case "6":
                            //论文发表
                            $count =  ActivityInfo::select('activity_info.*', 'b.status')
                                    ->leftJoin('form_instance as b' ,'activity_info.form_instance_id','=','b.id')
                                    ->leftJoin('participant as d','activity_info.id','=','d.activity_info_id')
                                    ->whereIn('activity_info.activity_category',['EW_PAPER_PUBLISH_TABLE','TEARRESEARCH_PUBLISH_TABLE'])
                                    ->where('b.status','PASS')
                                    ->whereIn('activity_info.data_version_id',$check['materials_array'])
                                    ->whereRaw(" JSON_EXTRACT(activity_info.`pubications_publish_type`,'$[0].optionId') = 128")
                                    ->where('activity_info.deleted', 0)
                                    // ->when($user_id, function($q) use($user_id){
                                    //     $q->whereHas('participants', function($query) use($user_id){
                                    //         $query->where('user_id', $user_id);
                                    //     });
                                    // })
                                    ->whereRaw("(d.user_id={$user_id})")
                                    ->groupBy('activity_info.id')
                                    ->get()->count();
                                    $table_data[$key]['module_info'][$k]['module_id'] = $v;
                                    $table_data[$key]['module_info'][$k]['count'] = $count;
                                    $table_data[$key]['module_info'][$k]['name'] = '论文发表';
                                    $table_data[$key]['module_info'][$k]['materials_array'] = json_encode($check['materials_array']);
                            break;
                        case "7":
                            //论著发表
                           // DB::connection()->enableQueryLog();
                            $count =  ActivityInfo::select('activity_info.*', 'b.status')
                                    ->leftJoin('form_instance as b' ,'activity_info.form_instance_id','=','b.id')
                                    ->leftJoin('participant as d','activity_info.id','=','d.activity_info_id')
                                    ->whereIn('activity_info.activity_category',['EW_PAPER_PUBLISH_TABLE','TEARRESEARCH_PUBLISH_TABLE'])
                                    ->where('b.status','PASS')
                                    ->whereIn('activity_info.data_version_id',$check['materials_array'])
                                    ->whereRaw(" JSON_EXTRACT(activity_info.`pubications_publish_type`,'$[0].optionId') = 129")
                                    ->where('activity_info.deleted', 0)
                                    // ->when($user_id, function($q) use($user_id){
                                    //     $q->whereHas('participants', function($query) use($user_id){
                                    //         $query->where('user_id', $user_id);
                                    //     });
                                    // })
                                    ->whereRaw("(activity_info.creator_id = {$user_id} or d.user_id={$user_id})")
                                    ->count();
                                   // dd(DB::getQueryLog());
                                    $table_data[$key]['module_info'][$k]['module_id'] = $v;
                                    $table_data[$key]['module_info'][$k]['count'] = $count;
                                    $table_data[$key]['module_info'][$k]['name'] = '论著发表';
                                    $table_data[$key]['module_info'][$k]['materials_array'] = json_encode($check['materials_array']);
                            break;
                        case "19":
                            //带教情况
                            //$username =  User::where('id',$user_id)->value('nickname');
                            $count =  ActivityInfo::select('activity_info.*')
                                    ->leftJoin('form_instance as b' ,'activity_info.form_instance_id','=','b.id')
                                    ->leftJoin('event_statistics as c','c.activity_info_id','=','activity_info.id')
                                    ->leftJoin('participant as d','activity_info.id','=','d.activity_info_id')
                                    ->where('activity_info.activity_category','IMPORTANTWORK_TEACHING_SITUATION_TABLE')
                                    ->where('b.status','PASS')
                                    ->whereIn('activity_info.data_version_id',$check['materials_array'])
                                    ->where('activity_info.deleted', 0)
                                    //->whereRaw("(activity_info.teacher_name like '%{$username}%' or d.user_id={$user_id})")
                                    ->where('activity_info.creator_id',$user_id)
                                    ->count();
                            $table_data[$key]['module_info'][$k]['module_id'] = $v;
                            $table_data[$key]['module_info'][$k]['count'] = $count;
                            $table_data[$key]['module_info'][$k]['name'] = '带教情况';
                            $table_data[$key]['module_info'][$k]['materials_array'] = json_encode($check['materials_array']);
                            break;
                    }
               }
            }

            if($scores && isset($scores[$value['id']]) && $checkTeacher['is_check']==1){
                $value['pf_score_json'] = $scores[$value['id']];
                $value['pf_score_json']['pj_scores'] = round($scores[$value['id']]['scores_sum']/count($scores[$value['id']]['scores']),2) ;
            }else{
                $value['pf_score_json'] = '';
            }
        }

        $checkTeacher = $checkTeacher?$checkTeacher->toArray():[];
       // dd($checkTeacher);
        return [
            'table_data' => $table_data,
            'zf' => $checkTeacher && $checkTeacher['is_check']==1?$checkTeacher['scores']:'',
            'pjf' => $checkTeacher && $checkTeacher['is_check']==1?$checkTeacher['average_score']:'',
            'pm' => $checkTeacher && $checkTeacher['is_check']==1?$this->get_ranks($id,$user_id,$checkTeacher['title_level'],$checkTeacher['id']):'',
        ];
    }

    /**
     * 获取审核小组评分
     * @author lxy
     * @datetime 2023-07-13
     */
    public function getCheckScores($request)
    {
        $scale_id = $request['scale_id'];
        $id = $request['id'];
        $user_id = $request['user_id'];
        $p_user_id = $request['p_user_id'];
        $ct_id = $request['ct_id'];

        $check = Check::where('id',$id)->first()->toArray();

        $ScaleDetail = ScaleDetail::whereIsEnd(1)
                    ->where('scale_id',$scale_id)
                    ->orderBy('top_id', 'asc')
                    ->orderBy('sort', 'asc')
                    ->orderBy('pid', 'asc')
                    ->with('parent')
                    ->get()->toArray();

        /** @var \App\Repositories\System\ScaleRepository $scaleRepository */
        $scaleRepository = app('scaleRepository');
        $table_data = $scaleRepository->toTableData2($ScaleDetail);

        //获取当前考评人员得分
        $checkScore = CheckScore::where('ct_id',$ct_id)->select('scores_detail','scores','average_score')->where('author_id',$p_user_id)->first();
        $checkScore = $checkScore?$checkScore->toArray():[];
        $zf = $checkScore?$checkScore['scores']:'';
        $pjf = $checkScore?$checkScore['average_score']:'';
        $scores = [];
        $scores = $checkScore?array_column($checkScore['scores_detail_array'],null,'scale_id'):[];
        //dd($scores);
        foreach($table_data as $key=>&$value){

            if($value['check_module_text'] != '无'){
                foreach(json_decode($table_data[$key]['check_module'],true) as $k=>$v ){
                    switch ($v){
                        case "4":
                            //经验交流
                            $count =  ActivityInfo::select('activity_info.*', 'b.status')
                                    ->leftJoin('form_instance as b' ,'activity_info.form_instance_id','=','b.id')
                                    ->whereIn('activity_info.activity_category',['TEARRESEARCH_EXCHANGE_TABLE','EW_EXCHANGE_EXPERIENCE_TABLE'])
                                    ->where('b.status','PASS')
                                    ->whereIn('activity_info.data_version_id',$check['materials_array'])
                                    ->where('activity_info.deleted', 0)
                                    // ->when($user_id, function($q) use($user_id){
                                    //     $q->whereHas('participants', function($query) use($user_id){
                                    //         $query->where('user_id', $user_id)->where('');
                                    //     });
                                    // })
                                    ->where('activity_info.creator_id',$user_id)
                                    ->get()->count();
                                    $table_data[$key]['module_info'][$k]['module_id'] = $v;
                                    $table_data[$key]['module_info'][$k]['count'] = $count;
                                    $table_data[$key]['module_info'][$k]['name'] = '经验交流';
                                    $table_data[$key]['module_info'][$k]['materials_array'] = json_encode($check['materials_array']);
                            break;
                        case "5":
                            //课题研究
                            //DB::connection()->enableQueryLog();
                            $count = ActivityInfo::select('activity_info.*', 'ss.status', 'b.status as old_status')
                                    ->leftJoin('form_instance as b' ,'activity_info.form_instance_id','=','b.id')
                                    ->leftJoin('participant as d','activity_info.id','=','d.activity_info_id')
                                    ->leftJoin('study_stage as ss', function ($join) {
                                        //$join->on('activity_info.id', '=', 'ss.activity_id')->on('activity_info.stage', '=', 'ss.stage');
                                        $join->on('activity_info.id', '=', 'ss.activity_id');
                                    })
                                    ->whereIn('activity_info.activity_category',['TEARRESEARCH_SUBJECT_STUDY_TABLE','EW_SUBJECT_RESEARCH_TABLE'])
                                    ->whereIn('activity_info.data_version_id',$check['materials_array'])
                                    ->whereRaw(" ( (ss.stage =4 and ss.status = 2 ) or  b.status = 'PASS') ")
                                    ->where('activity_info.deleted', 0)
                                    // ->when($user_id, function($q) use($user_id){
                                    //     $q->whereHas('participants', function($query) use($user_id){
                                    //         $query->where('user_id', $user_id);
                                    //     });
                                    // })
                                    ->whereRaw("(d.user_id={$user_id})")
                                    ->groupBy('activity_info.id')
                                    ->get()->count();
                                    //dd(DB::getQueryLog());
                                    $table_data[$key]['module_info'][$k]['module_id'] = $v;
                                    $table_data[$key]['module_info'][$k]['count'] = $count;
                                    $table_data[$key]['module_info'][$k]['name'] = '课题研究';
                                    $table_data[$key]['module_info'][$k]['materials_array'] = json_encode($check['materials_array']);
                            break;
                        case "6":
                            //论文发表
                            $count =  ActivityInfo::select('activity_info.*', 'b.status')
                                    ->leftJoin('form_instance as b' ,'activity_info.form_instance_id','=','b.id')
                                    ->leftJoin('participant as d','activity_info.id','=','d.activity_info_id')
                                    ->whereIn('activity_info.activity_category',['EW_PAPER_PUBLISH_TABLE','TEARRESEARCH_PUBLISH_TABLE'])
                                    ->where('b.status','PASS')
                                    ->whereIn('activity_info.data_version_id',$check['materials_array'])
                                    ->whereRaw(" JSON_EXTRACT(activity_info.`pubications_publish_type`,'$[0].optionId') = 128")
                                    ->where('activity_info.deleted', 0)
                                    // ->when($user_id, function($q) use($user_id){
                                    //     $q->whereHas('participants', function($query) use($user_id){
                                    //         $query->where('user_id', $user_id);
                                    //     });
                                    // })
                                    ->whereRaw("(d.user_id={$user_id})")
                                    ->groupBy('activity_info.id')
                                    ->get()->count();
                                    $table_data[$key]['module_info'][$k]['module_id'] = $v;
                                    $table_data[$key]['module_info'][$k]['count'] = $count;
                                    $table_data[$key]['module_info'][$k]['name'] = '论文发表';
                                    $table_data[$key]['module_info'][$k]['materials_array'] = json_encode($check['materials_array']);
                            break;
                        case "7":
                            //论著发表
                           // DB::connection()->enableQueryLog();
                            $count =  ActivityInfo::select('activity_info.*', 'b.status')
                                    ->leftJoin('form_instance as b' ,'activity_info.form_instance_id','=','b.id')
                                    ->leftJoin('participant as d','activity_info.id','=','d.activity_info_id')
                                    ->whereIn('activity_info.activity_category',['EW_PAPER_PUBLISH_TABLE','TEARRESEARCH_PUBLISH_TABLE'])
                                    ->where('b.status','PASS')
                                    ->whereIn('activity_info.data_version_id',$check['materials_array'])
                                    ->whereRaw(" JSON_EXTRACT(activity_info.`pubications_publish_type`,'$[0].optionId') = 129")
                                    ->where('activity_info.deleted', 0)
                                    // ->when($user_id, function($q) use($user_id){
                                    //     $q->whereHas('participants', function($query) use($user_id){
                                    //         $query->where('user_id', $user_id);
                                    //     });
                                    // })
                                    ->whereRaw("(activity_info.creator_id = {$user_id} or d.user_id={$user_id})")
                                    ->count();
                                   // dd(DB::getQueryLog());
                                    $table_data[$key]['module_info'][$k]['module_id'] = $v;
                                    $table_data[$key]['module_info'][$k]['count'] = $count;
                                    $table_data[$key]['module_info'][$k]['name'] = '论著发表';
                                    $table_data[$key]['module_info'][$k]['materials_array'] = json_encode($check['materials_array']);
                            break;
                        case "19":
                            //带教情况
                            //$username =  User::where('id',$user_id)->value('nickname');
                            $count =  ActivityInfo::select('activity_info.*')
                                    ->leftJoin('form_instance as b' ,'activity_info.form_instance_id','=','b.id')
                                    ->leftJoin('event_statistics as c','c.activity_info_id','=','activity_info.id')
                                    ->leftJoin('participant as d','activity_info.id','=','d.activity_info_id')
                                    ->where('activity_info.activity_category','IMPORTANTWORK_TEACHING_SITUATION_TABLE')
                                    ->where('b.status','PASS')
                                    ->whereIn('activity_info.data_version_id',$check['materials_array'])
                                    ->where('activity_info.deleted', 0)
                                    //->whereRaw("(activity_info.teacher_name like '%{$username}%' or d.user_id={$user_id})")
                                    ->where('activity_info.creator_id',$user_id)
                                    ->count();
                            $table_data[$key]['module_info'][$k]['module_id'] = $v;
                            $table_data[$key]['module_info'][$k]['count'] = $count;
                            $table_data[$key]['module_info'][$k]['name'] = '带教情况';
                            $table_data[$key]['module_info'][$k]['materials_array'] = json_encode($check['materials_array']);
                            break;
                    }
               }
            }

            if($scores && isset($scores[$value['id']])){
                $value['pf_score_json'] = $scores[$value['id']];
                $value['pf_score_json']['pj_scores'] = $scores[$value['id']]['scores'];
            }else{
                $value['pf_score_json'] = '';
            }
        }
        $checkTeacher = CheckTeacher::where('id',$ct_id)->first();
        $checkTeacher = $checkTeacher?$checkTeacher->toArray():[];
        return [
            'table_data' => $table_data,
            'zf' => $zf,
            'pjf' => $pjf,
            'pm' => $checkTeacher && $checkTeacher['is_check']==1?$this->get_ranks($id,$user_id,$checkTeacher['title_level'],$checkTeacher['id']):'',
        ];

    }

     /**
     * 排序
     * @author lxy
     * @datetime 2023-07-13
     */
    public function setCheckSort($request)
    {
        $this_sort_id = $request['this_sort_id'];
        $this_id = $request['this_id'];
        $sort_id = $request['sort_id'];
        $sort_s_id = $request['sort_s_id'];

        try{
            DB::beginTransaction();
            CheckTeacher::where('id',$this_id)->update(['sort'=>$sort_s_id]);
            CheckTeacher::where('id',$sort_id)->update(['sort'=>$this_sort_id]);

            DB::commit();
            return [];
        }catch(\Exception $e){
            DB::rollback();
            throw new \Exception($e->getMessage());
        }

    }

    /**
     * 删除
     * @author lxy
     * @datetime 2023-07-14
     */
    public function delCheck($request)
    {
        $id = $request['id'];
        $retult = Check::where('id',$id)->delete();
        CheckTeacher::where('check_id',$id)->delete();
        CheckScore::where('check_id',$id)->delete();
        CheckScoresDetail::where('check_id',$id)->delete();
        return $retult;
    }

    /**
     * 审核列表导出
     * @author lxy
     * @datetime 2023-07-14
     */
    public function checkTeacherExport($request)
    {
        $id = $request['id'];
        $export_type = !empty($request['export_type'])?$request['export_type']:'';
        $res = $request['checkStatus']['data'];

        $user = auth()->user();

        $check = Check::where('id',$id)->first()->toArray();

        $ScaleDetail = ScaleDetail::whereIsEnd(1)
                    ->where('scale_id',$check['scale_id'])
                    ->orderBy('top_id', 'asc')
                    ->orderBy('sort', 'asc')
                    ->orderBy('pid', 'asc')
                    ->with('parent')
                    ->get()->toArray();

        /** @var \App\Repositories\System\ScaleRepository $scaleRepository */
        $scaleRepository = app('scaleRepository');
        $table_data = $scaleRepository->toTableData2($ScaleDetail);

        $tc = count($table_data);
        $else_data[] =  $table_data[$tc-3]; //其他指标
        $else_data[] =  $table_data[$tc-2];
        $else_data[] =  $table_data[$tc-1];
        unset($table_data[$tc-3],$table_data[$tc-2],$table_data[$tc-1]);
        $level1_data = array_values(array_column($table_data,null,'title1'));  //一级指标
        $level2_data = $table_data;  //二级指标
        //dd($level1_data);

        $zip_path = public_path().'/download/zip'; //zip路径
        if(!empty($request['file_name'])){
            $file_name = $request['file_name'].'_'.date('YmdHis').'.zip';
        }else{
            $file_name = '考核教师_'.date('YmdHis').'.zip';
        }
        $zip_filename = $zip_path.'/'.$file_name; //zip名称加路径
        $zip = new \ZipArchive();

        foreach($res as $rk=>&$rv){
            $rv['title_name2'] = $rv['title_name'];// @tips 2024-07-19 把正高级教师改成高级教师
            if($rv['title_name2'] == '正高级教师(属于高级)'){
                $rv['title_name2'] = '高级教师';
            }
            if($rv['is_check'] == 0){
                unset($res[$rk]);
            }
        }

        if(empty($res)){
            throw new \Exception('必须审核完整的数据才能导出');
        }

        if($zip->open($zip_filename, \ZipArchive::CREATE | \ZipArchive::OVERWRITE) === true){
            foreach($res as $k=>$v){
                $exportData = [];
                if($v['is_check'] == 1){
                    //获取指标分数
                    $scale_scores = $this->get_scale_scores($v['id'],$id);

                    //获取职务排名
                    $title = $this->get_titles($v['id'],$id);


                    // $sublect_grade = null;
                    // if(!empty($title['subject'])){
                    //     $sublect_grade = "任教学科:{$title['subject']}    年级班级:{$title['grade']}";
                    // }else{
                    //     $sublect_grade = "任教学科:                  年级班级:{$title['grade']}";
                    // }
                    $exportData[] = [0=>'中小学教师(班主任)任职情况考评汇总表'];
                    $exportData[] = [0=>'编号：'];
                    //$exportData[] = [0=>"被考评者姓名：{$title['username']}",1=>null,2=>$sublect_grade,3=>null,4=>null,5=>"  任班主任班级：{$title['class']}",6=>null,7=>null,8=>null];
                    $exportData[] = [0=>"被考评者姓名：{$title['username']}",1=>null,2=>null,3=>null,4=>"年级班级：{$title['grade']}",5=>null,6=>null,7=>null,8=>null];
                    $exportData[] = [0=>"任班主任班级：{$title['class']}",1=>null,2=>null,3=>null,4=>"任教学科：{$title['subject']}",5=>null,6=>null,7=>null,8=>null];
                    $exportData[] = [0=>'序号',1=>'二  级  指  标  名  称',2=>'分值',3=>'序号',4=>'一  级  指  标  名  称',5=>'分值',6=>'名次',7=>'校最高分',8=>'校平均分'];

                    foreach($level2_data as $k2=>$v2){
                        //dd($level1_data);
                        //二级指标分数
                        $ej_score = isset($scale_scores['ej_level'][$v2['id']]['pj_scores'])?(string)$scale_scores['ej_level'][$v2['id']]['pj_scores']:0;
                        //一级指标分数
                        if($k2 >=1 && $k2 <=7){
                            $yj_score = isset($scale_scores['yj_level'][$v['id']][$level1_data[$k2]['top_id']]['pj_scores'])?$scale_scores['yj_level'][$v['id']][$level1_data[$k2]['top_id']]['pj_scores']:null;
                        }else{
                            $yj_score = null;
                        }

                        if($k2 == 0){
                            $exportData[] = [0=>$k2+1,1=>$v2['title2'],
                            2=>$ej_score && $ej_score>=10?'优等':$ej_score,
                            3=>$this->get_serial($k2),
                            4=>!empty($level1_data[$k2])?$level1_data[$k2]['title1']:null,
                            5=>$ej_score && $ej_score>=10?'优等':$ej_score,6=>'--',7=>'--',8=>'--'];
                        }else if($k2 == 8 ){
                            $exportData[] = [0=>$k2+1,1=>$v2['title2'],
                            2=>$ej_score,
                            3=>$else_data[0]['title2'],4=>null,
                            5=>isset($scale_scores['ej_level'][$else_data[0]['id']]['pj_scores'])?(string)$scale_scores['ej_level'][$else_data[0]['id']]['pj_scores']:0,
                            6=>'--',7=>'--',8=>'--'];
                        }else if($k2 == 9){
                            $exportData[] = [0=>$k2+1,1=>$v2['title2'],
                            2=>$ej_score,
                            3=>$else_data[1]['title2'],4=>null,
                            5=>isset($scale_scores['ej_level'][$else_data[1]['id']]['pj_scores'])?(string)$scale_scores['ej_level'][$else_data[1]['id']]['pj_scores']:0,
                            6=>'--',7=>'--',8=>'--'];
                        }else if($k2 == 10){
                            $exportData[] = [0=>$k2+1,1=>$v2['title2'],
                            2=>$ej_score,
                            3=>$else_data[2]['title2'],4=>null,
                            5=>isset($scale_scores['ej_level'][$else_data[2]['id']]['pj_scores'])?(string)$scale_scores['ej_level'][$else_data[2]['id']]['pj_scores']:0,
                            6=>'--',7=>'--',8=>'--'];
                        }else if($k2 == 11){
                            $ds_score = isset($scale_scores['ds_score'][$v['id']])?$scale_scores['ds_score'][$v['id']]:'0';
                            $ds_mc = isset($scale_scores['ds_zg_mc']) && in_array($ds_score,$scale_scores['ds_zg_mc'])?array_search($ds_score,$scale_scores['ds_zg_mc']):'0';
                            $zg_score = isset($scale_scores['ds_zg_mc']) && in_array($ds_score,$scale_scores['ds_zg_mc'])?$scale_scores['ds_zg_mc'][0]:'0';
                            $exportData[] = [0=>$k2+1,1=>$v2['title2'],
                            2=>$ej_score,
                            3=>'德识能绩责总分',4=>null,
                            5=>$ds_score,
                            6=>++$ds_mc,
                            7=>$zg_score,
                            8=>isset($scale_scores['ds_pj_score'])?$scale_scores['ds_pj_score']:'0',];
                        }else if($k2 == 12){
                            $exportData[] = [0=>$k2+1,1=>$v2['title2'],
                            2=>$ej_score,
                            3=>"被考评者申报中学     级教师职务          考评{$title['title']}共 {$title['sum']} 人",4=>null,
                                    5=>null,6=>null,7=>null,8=>null];
                        }else if($k2 == 13){
                            $exportData[] = [0=>$k2+1,1=>$v2['title2'],
                            2=>$ej_score,
                            3=>"被考评者申报小学     级教师职务          该教师第 {$title['rank']} 名 {$title['zb']}%",4=>null,
                                    5=>null,6=>null,7=>null,8=>null];
                        }else if($k2 == 14){
                            $exportData[] = [0=>$k2+1,1=>$v2['title2'],
                            2=>$ej_score,
                            3=>"本校申报该级教师职务共 {$title['sum']} 人",4=>null,
                                    5=>null,6=>null,7=>null,8=>null];
                        }else if($k2 == 15){
                            $dxyj = $title['dxyj'] == 1?'优秀':'合格';
                            $exportData[] = [0=>$k2+1,1=>$v2['title2'],
                            2=>$ej_score,
                            3=>"\n定性意见：{$dxyj}",
                            4=>null,5=>null,6=>null,7=>null,8=>null];
                        }else{
                            if($k2 >=1 && $k2 <=7){
                                $alb_number = $this->get_serial($k2);
                                $rank = isset($scale_scores['x_zg_score'][$level1_data[$k2]['top_id']]) && in_array($yj_score,$scale_scores['x_zg_score'][$level1_data[$k2]['top_id']])?array_search($yj_score,$scale_scores['x_zg_score'][$level1_data[$k2]['top_id']]):null;
                                $zg_score = isset($scale_scores['x_zg_score'][$level1_data[$k2]['top_id']]) && in_array($yj_score,$scale_scores['x_zg_score'][$level1_data[$k2]['top_id']])?$scale_scores['x_zg_score'][$level1_data[$k2]['top_id']][0]:null;
                                $pj_score = isset($scale_scores['x_pj_score'][$level1_data[$k2]['top_id']])?$scale_scores['x_pj_score'][$level1_data[$k2]['top_id']]['pj_scores']:null;
                            }else{
                                $rank = null;
                                $zg_score = null;
                                $pj_score = null;
                                $alb_number = null;
                            }
                            $exportData[] = [0=>$k2+1,1=>$v2['title2'],
                            2=>$ej_score,
                            3=>$alb_number,4=>!empty($level1_data[$k2])?$level1_data[$k2]['title1']:null,
                            5=>$yj_score,
                            6=>++$rank,7=>$zg_score,8=>$pj_score];
                        }
                    }

                    $exportData[] = [0=>'区县：上海市徐汇区',1=>null,2=>'单位：上海市徐汇区汇师小学(盖章)',3=>null,4=>null,
                                    5=>'填表人:               日期:'.date('Y').'-08-15',6=>null,7=>null,8=>null];

                    //导出
                    $xlsx_file_name = $user->id.'/'.md5($v['user_id'].time()).'.xlsx';
                    Excel::store(new CheckExport($exportData), $xlsx_file_name, 'excel');
                    //dd(1);
                    //xlsx文件路径，zip内文件路径名称
                    if($export_type == 'all'){
                        $zip->addFile(public_path('download/excel').'/'.$xlsx_file_name , '学年考核/'.$v['title_name2'].'/'.$v['username'].'_'.$title['rank'].'.xlsx');
                    }else{
                        $zip->addFile(public_path('download/excel').'/'.$xlsx_file_name , $v['username'].'_'.$title['rank'].'.xlsx');
                    }
                }
            }
        }
        @$zip->close();

        //删除目录以及文件
        $this->deleteDir(public_path('download/excel').'/'.$user->id);

        if(file_exists($zip_filename)){
            return [
                'file_name' => $file_name,
            ];
        }else{
            throw new \Exception('导出失败');
        }
    }

    /**
     * 导出
     * @author lxy
     * @datetime 2023-07-19
     */
    public function checkExport($request)
    {
        $id = $request['id'];
        $res = CheckTeacher::where('check_id',$id)->where('is_check',1)->with('checks:id,name')->get()->toArray();
        if(empty($res)){
            throw new \Exception('没有导出的数据');
        }
        $result =  $this->checkTeacherExport(['id'=>$id,'export_type'=>'all','file_name'=>$res[0]['checks']['name'],'checkStatus'=>['data'=>$res]]);
        return $result;
    }

    /**
     * 汇总表导出
     * @author lxy
     * @datetime 2023-08-11
     */
    public function finalExport($request)
    {
        ini_set('memory_limit','256M');    // 临时设置最大内存占用
        set_time_limit(120);   // 设置脚本最大执行时间

        $where = [];

        !empty($request['search']['data_version_id'])   && $where[] = ['data_version_id','=',$request['search']['data_version_id']];

        !empty($request['search']['name'])     && $where[] = ['name','like','%'.$request['search']['name'].'%'];

        if(isset($request['search']['status']) &&  $request['search']['status'] === '0'){
            $where[] = ['status','=',$request['search']['status']];
        }else if(isset($request['search']['status']) &&  $request['search']['status'] == 1){
            $where[] = ['status','=',1];
            $where[] = ['check_start','>',date('Y-m-d H:i:s')];
        }else if(isset($request['search']['status']) &&  $request['search']['status'] == 2){
            $where[] = ['status','=',1];
            $where[] = ['check_start','<=',date('Y-m-d H:i:s')];
            $where[] = ['check_end','>=',date('Y-m-d H:i:s')];
        }else if(isset($request['search']['status']) &&  $request['search']['status'] == 3){
            $where[] = ['status','=',1];
            $where[] = ['check_end','>',date('Y-m-d H:i:s')];
        }

        if(!empty($request['search']['date'])){
            $date = explode(' - ',$request['search']['date']);
            $where[] = ['check_start','>=',$date[0]];
            $where[] = ['check_end','<=',$date[1]];
        }


        $check_id = Check::where($where)
                    ->orderBy('created_at','desc')
                    ->pluck('id')->toArray();


        $user = auth()->user();
        //$data_version_id = $request['data_version_id'];
        $data_version_name = $request['data_version_name'];
        $checks =  CheckTeacher::select('b.name','b.leader','b.members','check_teachers.*')
                    ->leftJoin('checks as b','check_teachers.check_id','=','b.id')
                    ->with('check_scores:check_id,ct_id,scores,author_id')
                    //->where('b.data_version_id',$data_version_id)
                    ->whereIn('check_id',$check_id)
                    ->where('check_teachers.status',1)
                    ->where('check_teachers.is_check',1)
                    ->where('check_teachers.title_level','!=',0)
                    ->orderBy('check_teachers.title_level','asc')
                    ->orderBy('check_teachers.scores','desc')->orderBy('check_teachers.check_time','asc')
                    ->get()->toArray();

        if(empty($checks) || empty($check_id)){
            throw new \Exception('没有导出的数据');
        }
        $result = [];
        foreach($checks as &$v){
            $v['title_name2'] = $v['title_name'];  // @tips 2024-07-19 把正高级教师改成高级教师
            if($v['title_name2'] == '正高级教师(属于高级)'){
                $v['title_name2'] = '高级教师';
            }
            $result[$v['name']][$v['title_name2']][] = $v;
        }

        $zip_path = public_path().'/download/zip'; //zip路径
        $file_name = $data_version_name.'_'.date('YmdHis').'.zip';
        $zip_filename = $zip_path.'/'.$file_name; //zip名称加路径
        $zip = new \ZipArchive();
        //dd($result);
        if($zip->open($zip_filename, \ZipArchive::CREATE | \ZipArchive::OVERWRITE) === true){
            foreach($result as $k=>$v){
                //dd($k,$v);
                foreach($v as $k2=>$v2){
                    $exportData = [];
                    //dd($k2,$v2);
                    foreach($v2 as $k3=>$v3){
                        //获取职务排名
                        $title = $this->get_titles($v3['id'],$v3['check_id']);
                        $leader = json_decode($v3['leader'],true);
                        $members = json_decode($v3['members'],true);
                        $cs =  array_column($v3['check_scores'],null,'author_id');

                        if(empty($exportData)){
                            $exportData[$k3][] = '老师姓名';
                            $exportData[$k3][] = '职称';
                            $exportData[$k3][] = '任教学科';
                            $exportData[$k3][] = '任教年级';
                            $exportData[$k3][] = '班主任任班级';
                            $exportData[$k3][] = '最终评分';
                            $exportData[$k3][] = '最终排名';
                            foreach($leader as $le){
                                $exportData[$k3][] = $le['name'].'(组长)';
                            }
                            foreach($members as $me){
                                $exportData[$k3][] = $me['name'];
                            }

                        }
                        $exportData[$k3+1][] = $v3['username'];
                        $exportData[$k3+1][] = $v3['title_name'];
                        $exportData[$k3+1][] = $title['subject'];
                        $exportData[$k3+1][] = $title['grade'];
                        $exportData[$k3+1][] = $title['class'];
                        $exportData[$k3+1][] = $v3['scores']." ";
                        $exportData[$k3+1][] = $title['rank'].'/'.$title['sum'];
                        //var_dump($leader,$members);
                        foreach($leader as $le){
                            $pm = $this->get_ranks2($v3['check_id'],$v3['user_id'],$v3['title_level'],$v3['id'],$le['value']);
                            //var_dump($pm);
                            $exportData[$k3+1][] = !empty($cs[$le['value']])?$cs[$le['value']]['scores']."({$pm['gr_rank']}/{$pm['rank_sum']})":0 ."({$pm['gr_rank']}/{$pm['rank_sum']})";
                        }
                        foreach($members as $me){
                            //dd($members);
                            $pm = $this->get_ranks2($v3['check_id'],$v3['user_id'],$v3['title_level'],$v3['id'],$me['value']);
                            $exportData[$k3+1][] = !empty($cs[$me['value']])?$cs[$me['value']]['scores']."({$pm['gr_rank']}/{$pm['rank_sum']})":0 ."({$pm['gr_rank']}/{$pm['rank_sum']})";
                        }
                    }

                    $xlsx_file_name = $user->id.'/'.md5($v3['id'].time()).'.xlsx';
	                Excel::store(new CheckExcelExport($exportData), $xlsx_file_name, 'excel');
                    //dd(1);
                    //原文件路径，zip内文件路径名称
                    $zip->addFile(public_path('download/excel').'/'.$xlsx_file_name , '学年考核/'.$k.'/'.$k2.'.xlsx');

                }

            }
        }
        @$zip->close();

        //删除目录以及文件
        if(file_exists($zip_filename)){
            return [
                'file_name' => $file_name,
            ];
        }else{
            throw new \Exception('导出失败');
        }
    }

    //删除目录及文件
    public function deleteDir($dir){
        if(!$handle = @opendir($dir)){return false;}
        while(false !== ($file = readdir($handle))){
            if ($file !== "." && $file !== ".."){       //排除当前目录与父级目录
                $file = $dir . '/' . $file;
                if (is_dir($file)) {
                    $this->deleteDir($file);
                } else {
                    @unlink($file);
                }
            }
        }
        @rmdir($dir);
    }

    //序号
    public function get_serial($k){
        switch($k)
        {
            case 0: return 'Ⅰ'; break;
            case 1: return 'Ⅱ'; break;
            case 2: return 'Ⅲ'; break;
            case 3: return 'Ⅳ'; break;
            case 4: return 'Ⅴ'; break;
            case 5: return 'Ⅵ'; break;
            case 6: return 'Ⅶ'; break;
            case 7: return 'Ⅷ'; break;
            default:return null;
        }
    }

    //职务以及其他信息
    public function get_titles($id,$check_id){
        $check = Check::where('id',$check_id)->first()->toArray();

        $check_teacher = CheckTeacher::where('id',$id)->with(['teaching_subject'=>function($q) use($check){
                            $q->where('data_version_id',$check['data_version_id'])->where('deleted',0)
                            ->select('user_id','subject_dic_items','grade_dic_items','class_item');
                        },'master_teacher'=>function($q) use($check){
                            $q->where('data_version_id',$check['data_version_id'])->where('deleted',0)
                            ->select('user_id','grade_value','class_value');
                        }])->first()->toArray();

        //获取主学科
        $Teachers = Teachers::where('user_id',$check_teacher['user_id'])->where('deleted', 0)->first();
        if($Teachers){
            $Teachers = $Teachers->toArray();
        }

        //年级班级
        $grade = '';
        if(!empty($check_teacher['teaching_subject'])){
            // foreach($check_teacher['teaching_subject'] as $gv){
            //     $grade .= !empty($gv['grade_set'][0]['selectValue'])?$gv['grade_set'][0]['selectValue'].(!empty($gv['class_set']['classes']) && $gv['class_set']['classes'] != '全年级'?$gv['class_set']['classes']:''):'';
            //     $grade .= ',';
            // }
            // $grade = trim($grade,',');
            $grade = !empty($check_teacher['grade_array'])?implode(',',array_column($check_teacher['grade_array'],'selectValue')):'';
        }

        $titles = CheckTeacher::where('check_id',$check_teacher['check_id'])->where('status',1)->where('title_level',$check_teacher['title_level'])->orderBy('is_check','desc')->orderBy('scores','desc')->get()->toArray();
            //dd($titles);
        //$zbrs =  ceil(count($titles)*0.3);   //各职级老师合格占比数
        $zbrs =  ceil(count($titles)*0.2);   //各职级老师合格占比数  @tips 2024-07-16 占比改成20% （已弃用）
        $rank = 0;
        foreach($titles as $k=>$v){
            if($v['id'] == $id){
                $rank = $k;
                break;
            }
        }

        $class = '';
        if(!empty($check_teacher['master_teacher']['class_set'])){
            if(!empty($check_teacher['master_teacher']['grade_set'])){
                $class = $check_teacher['master_teacher']['grade_set'][0]['selectValue'].$check_teacher['master_teacher']['class_set'][0]['selectValue'];
            }else{
                $class = $check_teacher['master_teacher']['class_set'][0]['selectValue'];
            }
        }else{
            $class = '';
        }

        $zb = round(($rank+1)/count($titles)*100,1); // @tips 2024-07-17  按占比来算定性意见
        if($zb > 20){
            $dxyj = 2; //超过占比的定性意见全部是合格
        }else{
            $dxyj = $this->get_level($check_teacher['title_level'],$check_teacher['average_score']);
        }

        //定性意见
        // if($rank+1 > $zbrs){
        //     $dxyj = 2; //超过占比的定性意见全部是合格
        // }else{
        //     $dxyj = $this->get_level($check_teacher['title_level'],$check_teacher['average_score']);
        // }

        return [
            'title' => !empty($check_teacher['title_name'])?($check_teacher['title_name'] == '正高级教师(属于高级)'?'高级教师':$check_teacher['title_name']):'',
            'sum' => count($titles),
            'rank' => $rank+1,
            'zb' => $zb,
            'dxyj' =>$dxyj,
            'username'=>!empty($check_teacher['username'])?$check_teacher['username']:'',
            //'subject' =>!empty($check_teacher['teaching_subject']['subject_set'])?implode(',',array_column($check_teacher['teaching_subject']['subject_set'],'selectValue')):'',
            //'subject' =>!empty($check_teacher['subject_array'])?implode(',',array_column($check_teacher['subject_array'],'selectValue')):'',
            'subject' =>$Teachers && !empty($Teachers['major_subject_array'])?(!empty($Teachers['major_subject_array'][0])?$Teachers['major_subject_array'][0]['selectValue']:$Teachers['major_subject_array']['selectValue']):'',
            //'grade' => !empty($check_teacher['teaching_subject']['grade_set'])?implode(',',array_column($check_teacher['teaching_subject']['grade_set'],'selectValue')):'',
            'grade' => $grade,
            'class' => $class
        ];

    }

    //获取指标分数
    public function get_scale_scores($ct_id,$check_id){
        $checkTeacher = CheckTeacher::where('check_id',$check_id)->where('is_check',1)->select('id','average_score')->get()->toArray();
        $ct_id_in = array_column($checkTeacher,'id');

        $ds = array_column($checkTeacher,'average_score');
        $dss = array_unique($ds);
        rsort($dss);

        $dspj = round(array_sum(array_column($checkTeacher,'average_score'))/count(array_column($checkTeacher,'average_score')),2);

        //获取一级指标评分
        $checkScore = CheckScore::where('ct_id',$ct_id)->select('scores_detail')->get()->toArray();

        $scores = [];
        foreach($checkScore as $k=>$v){
            foreach($v['scores_detail_array'] as $k2=>$v2){
                $scores[$k2]['scale_id'] = $v2['scale_id'];
                $scores[$k2]['scores'][] = $v2['scores'];
                $scores[$k2]['scores_sum'] = array_sum($scores[$k2]['scores']);
                $scores[$k2]['pj_scores'] = round(array_sum($scores[$k2]['scores'])/count($scores[$k2]['scores']),2);
            }
        }
        $scores = $scores?array_column($scores,null,'scale_id'):[];
        //dd($scores);

        //获取二级指标的分数排名
        $checkScoresDetail = CheckScoresDetail::whereIn('ct_id',$ct_id_in)->select('ct_id','scale_top_id','scale_id','scores','author_id')->get()->toArray();

        $level1 = [];
        $level2 = []; //二级指标平均分

        foreach($checkScoresDetail as $k=>$v){
            $level1[$v['ct_id']][$v['scale_top_id']][$v['author_id']]['scores'][] = $v['scores'];
            $level1[$v['ct_id']][$v['scale_top_id']][$v['author_id']]['scores_sum'] = array_sum($level1[$v['ct_id']][$v['scale_top_id']][$v['author_id']]['scores']);
            $level1[$v['ct_id']][$v['scale_top_id']][$v['author_id']]['pj_scores'] = round(array_sum($level1[$v['ct_id']][$v['scale_top_id']][$v['author_id']]['scores'])/count($level1[$v['ct_id']][$v['scale_top_id']][$v['author_id']]['scores']),2);
        }

        $level_array = [];
        $pj_array = []; //校平均分
        foreach($level1 as $k=>$v){
            foreach($v as $k2=>$v2){
                //$level2[$k][$k2]['scores'] = array_column($v2,'pj_scores');
                $level2[$k][$k2]['pj_scores'] = round(array_sum(array_column($v2,'pj_scores'))/count(array_column($v2,'pj_scores')),2);
                $level_array[$k2][] = $level2[$k][$k2]['pj_scores'];
                $pj_array[$k2]['scores'][] = $level2[$k][$k2]['pj_scores'];
                $pj_array[$k2]['scores_sum'] = array_sum($pj_array[$k2]['scores']);
                $pj_array[$k2]['pj_scores'] = round($pj_array[$k2]['scores_sum']/count($pj_array[$k2]['scores']),2);
            }
        }
        //dd($level_array);

        foreach($level_array as $rak => &$rav){
            $a = array_unique($rav); //去重
            rsort($a);
            $rav =  $a; //数组排序
        }
        //dd($scores);
        return [
            'ej_level'=>$scores,
            'yj_level'=>$level2,
            'x_pj_score'=>$pj_array, //校平均分
            'x_zg_score'=>$level_array, //校最高分，名次
            'ds_score' => array_column($checkTeacher,'average_score','id'), //德识分数
            'ds_zg_mc' => $dss, //德识最高分 名次
            'ds_pj_score' => $dspj
        ];

    }

    //获取教师职称档次
    public function get_level($level,$pjf){
        switch($level){
            case 1: //正高级
                if($pjf>=9){
                    return 1; //优
                }
                // else if($pjf<9 && $pjf>=7){
                //     return 2; //合格
                // }else if($pjf<7 && $pjf>=6){
                //     return 3; //基本合格
                // }else if($pjf<6){
                //     return 4; //不合格
                // }
                else{
                    return 2; //合格
                }
                break;
            case 2: //高级
                if($pjf>=9){
                    return 1; //优
                }
                // else if($pjf<9 && $pjf>=7){
                //     return 2; //合格
                // }else if($pjf<7 && $pjf>=6){
                //     return 3; //基本合格
                // }else if($pjf<6){
                //     return 4; //不合格
                // }
                else{
                    return 2; //合格
                }
                break;
            case 3: //一级
                if($pjf>=8){
                    return 1; //优
                }
                // else if($pjf<8 && $pjf>=5){
                //     return 2; //合格
                // }else if($pjf<5 && $pjf>=4){
                //     return 3; //基本合格
                // }else if($pjf<4){
                //     return 4; //不合格
                // }
                else{
                    return 2; //合格
                }
                break;
            case 4: //二级
                if($pjf>=7){
                    return 1; //优
                }
                // else if($pjf<7 && $pjf>=4){
                //     return 2; //合格
                // }else if($pjf<4 && $pjf>=3){
                //     return 3; //基本合格
                // }else if($pjf<3){
                //     return 4; //不合格
                // }
                else{
                    return 2; //合格
                }
                break;
            case 5: //见习教师
                return 2;
                break;
            default:
                return 0;
        }
    }

    //获取教师排名
    public function get_ranks($id,$user_id,$title_level,$ct_id){
        static $checkTeacher = null;
        $p_user_id = auth()->user()->id;

        if(!$checkTeacher){
            $checkTeacher = CheckTeacher::where('check_teachers.check_id',$id)
            ->where('status',1)
            ->with(['check_score'=>function($q) use($p_user_id) {
                $q->where('author_id',$p_user_id)->select('ct_id','author_id','scores');
            }])
            ->select('id','scores','user_id','is_check','title_level','username')
            ->orderBy('is_check','desc')->orderBy('scores','desc')->orderBy('check_time','desc')
            ->get()
            ->toArray();
        }

        if($title_level == 0){
            //ranks当前等级下的排名 rank_sum当前等级下的排名总数  gr_rank个人排名
            return ['ranks'=>0,'rank_sum'=>0,'gr_rank'=>0];
        }
        $ck = $cs = [];
        foreach($checkTeacher as $k=>$v){
            $ck[$v['title_level']][] = $v;
            if($v['check_score']){
                $cs[$v['title_level']][] = $v['check_score'];
            }
        }

        $ranks_ = 0;
        $gr_rank = 0;

        if(!empty($ck[$title_level])){
            foreach($ck[$title_level] as $k=>$v){
                if($v['is_check'] == 1 && $v['scores'] != 0 && $user_id == $v['user_id']){
                    $ranks_ = ++$k;
                    break;
                }
            }
            if(!empty($cs[$title_level])){
                $cs = $cs[$title_level];
                usort($cs,function($a,$b){
                    if ($a['scores'] == $b['scores']) {
                        return 0;
                    }
                    return ($a['scores'] > $b['scores']) ? -1 : 1;
                });

                foreach($cs as $k2=>$v2){
                    if($v2['ct_id'] == $ct_id){
                        $gr_rank = ++$k2;
                        break;
                    }
                }
            }

            return ['ranks'=>$ranks_,'rank_sum'=>count($ck[$title_level]),'gr_rank'=>$gr_rank];
        }else{
            return ['ranks'=>0,'rank_sum'=>0,'gr_rank'=>0];
        }


    }

    //获取教师排名
    public function get_ranks2($id,$user_id,$title_level,$ct_id,$p_user_id=null){
        //static $checkTeacher = null;
        if(!$p_user_id){
            //审核小组成员id
            $p_user_id = auth()->user()->id;
        }
        //if(!$checkTeacher){
            $checkTeacher = CheckTeacher::where('check_teachers.check_id',$id)
            ->where('status',1)
            ->with(['check_score'=>function($q) use($p_user_id) {
                $q->where('author_id',$p_user_id)->select('ct_id','author_id','scores');
            }])
            ->select('id','scores','user_id','is_check','title_level')
            ->orderBy('is_check','desc')->orderBy('scores','desc')->orderBy('check_time','asc')
            ->get()
            ->toArray();
        //}

        if($title_level == 0){
            //ranks当前等级下的排名 rank_sum当前等级下的排名总数  gr_rank个人排名
            return ['ranks'=>0,'rank_sum'=>0,'gr_rank'=>0];
        }
        $ck = $cs = [];
        foreach($checkTeacher as $k=>$v){
            $ck[$v['title_level']][] = $v;
            if($v['check_score']){
                $cs[$v['title_level']][] = $v['check_score'];
            }
        }

        $ranks_ = 0;
        $gr_rank = 0;

        if(!empty($ck[$title_level])){
            foreach($ck[$title_level] as $k=>$v){
                if($v['is_check'] == 1 && $v['scores'] != 0 && $user_id == $v['user_id']){
                    $ranks_ = ++$k;
                    break;
                }
            }

            if(!empty($cs[$title_level])){
                $cs = $cs[$title_level];
                usort($cs,function($a,$b){
                    if ($a['scores'] == $b['scores']) {
                        return 0;
                    }
                    return ($a['scores'] > $b['scores']) ? -1 : 1;
                });

                foreach($cs as $k2=>$v2){
                    if($v2['ct_id'] == $ct_id){
                        $gr_rank = ++$k2;
                        break;
                    }
                }
            }

            return ['ranks'=>$ranks_,'rank_sum'=>count($ck[$title_level]),'gr_rank'=>$gr_rank];
        }else{
            return ['ranks'=>0,'rank_sum'=>0,'gr_rank'=>0];
        }


    }

    //获取分数相同
    private function get_scores_eq($sheck_id,$user_id){
        $result = CheckTeacher::where('check_id',$sheck_id)->where('status',1)
                    ->with(['check_score'=>function($q) use($user_id){
                            $q->where('author_id',$user_id)->select('ct_id','author_id','scores');
                        }])
                    ->select('id','scores','is_check','title_level')
                    ->orderBy('sort')
                    ->get()
                    ->toArray();
        $a = $b = [];
        foreach($result as $k=>$v){
            if($v['is_check'] == 1){
                $a[$v['title_level']][] = $v['scores'];
            }
            if($v['check_score']){
                $b[$v['title_level']][] = $v['check_score']['scores'];
            }
        }
        foreach($a as &$av){
            foreach($av as &$av2){
                $av2 = (string)$av2.'_';
            }
        }
        foreach($a as &$av){
            $av = array_count_values($av);
        }
        foreach($b as &$bv){
            foreach($bv as &$bv2){
                $bv2 = (string)$bv2.'_';
            }
        }
        foreach($b as &$bv){
            $bv = array_count_values($bv);
        }
        return [
            'a' => $a, //审核组长所有评分相同
            'b' => $b,  //审核成员评分相同
        ];
    }

}
